IF OBJECT_ID('ssf.dpnd_derive_object_header') IS NOT NULL
	DROP PROCEDURE ssf.dpnd_derive_object_header
GO

CREATE PROCEDURE ssf.dpnd_derive_object_header
	@object_name NVARCHAR(256)
	, @object_header XML (ssf.object_header) = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Derive object header info from SQL Server meta data.
	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @sql VARCHAR(8000) -- or (MAX)
			, @nsql NVARCHAR(4000) -- or (MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@object_schema_name NVARCHAR(128)
			, @object_specific_name NVARCHAR(128)
			, @object_summary XML
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameter]]></code_comment>*/
SET @object_header = NULL
-- ============

-- ============
/*<code_comment><![CDATA[exit if null or blank object name]]></code_comment>*/
IF NULLIF(@object_name, N'') IS NULL
OR OBJECT_ID(@object_name) IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[parse object name]]></code_comment>*/
SET @object_schema_name = OBJECT_SCHEMA_NAME(OBJECT_ID(@object_name))
SET @object_specific_name = PARSENAME(@object_name, 1)
-- ============

-- ============
/*<code_comment><![CDATA[derive object summary]]></code_comment>*/
EXECUTE @proc_error = ssf.dpnd_derive_object_summary
	@object_name = @object_name
	, @object_summary = @object_summary OUTPUT

IF @proc_error <> 0
BEGIN
	RAISERROR('The call to the procedure <ssf.dpnd_derive_object_summary> failed.', 16, 1)
	RETURN @proc_error
END
-- ============

-- ============
/*<code_comment><![CDATA[derive parameter dependency]]></code_comment>*/
DECLARE @parameter_dependency_table TABLE (
	dpnd_object_name NVARCHAR(256)
	, parameter_order SMALLINT
	, parameter_name NVARCHAR(128)
	, data_type NVARCHAR(128)
	, return_type VARCHAR(16)
)

INSERT @parameter_dependency_table
SELECT ISNULL(SPECIFIC_SCHEMA + '.', '') + SPECIFIC_NAME AS dpnd_object_name
	, ORDINAL_POSITION AS parameter_order
	, PARAMETER_NAME AS parameter_name
	, CASE WHEN DATA_TYPE LIKE '%CHAR' OR DATA_TYPE LIKE '%BINARY'
		THEN UPPER(DATA_TYPE) + '(' + ISNULL(NULLIF(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR), '-1'), 'MAX') + ')'
		WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL')
		THEN UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
		WHEN DATA_TYPE IN ('FLOAT', 'REAL')
		THEN UPPER(DATA_TYPE) + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ')'
		WHEN DATA_TYPE IN ('DATETIME2', 'TIME')
		THEN UPPER(DATA_TYPE) + '(' + CAST(DATETIME_PRECISION AS VARCHAR) + ')'
		ELSE UPPER(DATA_TYPE)
		END AS data_type
	, CASE WHEN PARAMETER_MODE = 'IN'
		THEN 'INPUT'
		WHEN PARAMETER_MODE = 'OUT'
		THEN 'OUTPUT'
		WHEN PARAMETER_MODE = 'INOUT'
		THEN 'INPUT/OUTPUT'
		ELSE 'INPUT'
		END AS return_type
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = @object_schema_name
AND SPECIFIC_NAME = @object_specific_name
AND ORDINAL_POSITION > 0

SELECT @sql_error = @@ERROR
	, @row_count = @@ROWCOUNT

IF @sql_error <> 0
BEGIN
	RAISERROR('The INSERT into the table <@parameter_dependency_table> failed.', 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[derive input dependency]]></code_comment>*/
DECLARE @input_dependency_table TABLE (
	dpnd_object_name NVARCHAR(512)
	, object_type NVARCHAR(128)
)

BEGIN TRY
	INSERT @input_dependency_table
	SELECT ISNULL(r.referenced_server_name + '.', '')
			+ ISNULL(r.referenced_database_name + '.', '')
			+ ISNULL(r.referenced_schema_name + '.', '')
			+ r.referenced_entity_name AS dpnd_object_name
		, ISNULL(CASE WHEN o.type_desc LIKE '%PROCEDURE'
			THEN 'PROCEDURE'
			WHEN o.type_desc LIKE '%FUNCTION'
			THEN 'FUNCTION'
			WHEN o.type_desc LIKE '%TABLE'
			THEN 'TABLE'
			WHEN o.type_desc LIKE '%VIEW'
			THEN 'VIEW'
			ELSE o.type_desc
			END, 'UNKNOWN') AS object_type
	FROM sys.dm_sql_referenced_entities(@object_name, 'OBJECT') r
	LEFT JOIN sys.objects o
	ON r.referenced_entity_name = o.[name]
	AND ISNULL(r.referenced_schema_name, SCHEMA_NAME(o.[schema_id])) = SCHEMA_NAME(o.[schema_id])
	WHERE r.referenced_minor_name IS NULL
	AND r.is_ambiguous = 0
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()

	IF @sql_error <> 2020
	BEGIN
		RAISERROR('The INSERT into the table <@input_dependency_table> failed:%s%s', 16, 1, @crlf, @error_message)
		RETURN @sql_error
	END
END CATCH
-- ============

-- ============
/*<code_comment><![CDATA[derive referencing dependency]]></code_comment>*/
DECLARE @referencing_dependency_table TABLE (
	dpnd_object_name NVARCHAR(512)
	, object_type NVARCHAR(128)
)

INSERT @referencing_dependency_table
SELECT ISNULL(r.referencing_schema_name + '.', '')
		+ r.referencing_entity_name AS dpnd_object_name
	, ISNULL(CASE WHEN o.type_desc LIKE '%PROCEDURE'
		THEN 'PROCEDURE'
		WHEN o.type_desc LIKE '%FUNCTION'
		THEN 'FUNCTION'
		WHEN o.type_desc LIKE '%TABLE'
		THEN 'TABLE'
		WHEN o.type_desc LIKE '%VIEW'
		THEN 'VIEW'
		ELSE o.type_desc
		END, 'UNKNOWN') AS object_type
FROM sys.dm_sql_referencing_entities(@object_name, 'OBJECT') r
LEFT JOIN sys.objects o
ON r.referencing_entity_name = o.[name]
AND ISNULL(r.referencing_schema_name, SCHEMA_NAME(o.[schema_id])) = SCHEMA_NAME(o.[schema_id])

SELECT @sql_error = @@ERROR
	, @row_count = @@ROWCOUNT

IF @sql_error <> 0
BEGIN
	RAISERROR('The INSERT into the table <@referencing_dependency_table> failed.', 16, 1)
	RETURN @sql_error
END
-- ============

-- ============
/*<code_comment><![CDATA[construct xml output]]></code_comment>*/
BEGIN TRY
	SET @object_header = (
		SELECT @object_summary
			, (SELECT parameter_order, parameter_name, CAST(N'' AS NVARCHAR(2048)) AS parameter_desc, data_type, return_type
				FROM @parameter_dependency_table
				FOR XML RAW ('parameter_dependency'), ELEMENTS, TYPE)
			, (SELECT object_type, dpnd_object_name
				FROM @input_dependency_table
				FOR XML RAW ('input_dependency'), ELEMENTS, TYPE)
			, (SELECT object_type, dpnd_object_name
				FROM @referencing_dependency_table
				FOR XML RAW ('referencing_dependency'), ELEMENTS, TYPE)
		FOR XML RAW ('object_header'), ELEMENTS, TYPE
	)
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR('Failed trying to construct xml output:%s%s', 16, 1, @crlf, @error_message)
	RETURN @sql_error
END CATCH
-- ============

RETURN @@ERROR
GO
